package com.sigmazero13.bgjeeves.db;

import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public final class DbQueryRunner {
	
	public static final String TAG = "DbQueryRunner";
	
	public static void createTable(SQLiteDatabase db, DbTable table, Integer version) {
		Log.d(TAG, "Creating table " + table.getName() + " for version " + version);
		StringBuilder sb = new StringBuilder("CREATE TABLE " + table.getName() + "(");
		
		for (DbField field : table.getFields(version)) {
			sb.append(field.getColumnDef() + ", ");
		}
		sb.setLength(sb.length()-2);
		sb.append(")");
		
		db.execSQL(sb.toString());
	}
	
	public static void createTable(SQLiteDatabase db, DbTable table) {
		createTable(db, table, 999);
	}
	
	public static void addColumnToTable(SQLiteDatabase db, DbTable table, DbField column) {
		Log.d(TAG, "Adding column " + column.getName() + " to table " + table.getName());
		String SQL = "ALTER TABLE " + table.getName() + " ADD COLUMN " + column.getColumnDef();
		db.execSQL(SQL);
	}
	
	public static void dropTableColumnsForVersion(SQLiteDatabase db, DbTable table, Integer version) {
		Log.d(TAG, "Refactoring table " + table.getName() + " to version " + version);
		db.beginTransaction();
		
		try {
			String oldTableName = "__old__" + table.getName();
			
			String SQL = "ALTER TABLE " + table.getName() + " RENAME TO " + oldTableName;
			db.execSQL(SQL);
			
			createTable(db, table, version);
			
			StringBuilder columns = new StringBuilder();
			for (DbField field : table.getCurrentFields()) {
				columns.append(field.getName() + ", ");
			}
			columns.setLength(columns.length()-2);
			
			SQL = "INSERT INTO " + table.getName() + " (" + columns.toString() + ") " +
				"SELECT " + columns.toString() + " FROM " + oldTableName;
			db.execSQL(SQL);
			
			SQL = "DROP TABLE " + oldTableName;
			db.execSQL(SQL);
			
			db.setTransactionSuccessful();
		} catch (SQLException sqle) {
			Log.e(TAG, "Error dropping column", sqle);
		}
			
		db.endTransaction();
	}
	
	public static Cursor runQuery(SQLiteDatabase db, DbTable table, DbField[] columns, DbField orderBy) {
		return runQuery(db, table, columns, (DbField)null, null, orderBy);
	}
	
	public static Cursor runQuery(SQLiteDatabase db, DbTable table, DbField[] columns, DbField queryField, String queryValue, DbField orderBy) {
		String[] fields = new String[columns.length];
		String query = wrapQuery(queryField);
		String[] val = wrapValue(queryValue);
		String order = wrapOrder(orderBy);
		
		for (int i = 0; i < columns.length; i++) {
			fields[i] = columns[i].getName();
		}
		
		return db.query(true, table.getName(), fields, query, val, null, null, order, null);
	}
	
	public static Cursor runQuery(SQLiteDatabase db, DbTable table, DbField[] columns, DbField[] queryFields, String[] queryValues, DbField orderBy) {
		String[] fields = new String[columns.length];
		String query = wrapQueries(queryFields);
		String order = wrapOrder(orderBy);
		
		for (int i = 0; i < columns.length; i++) {
			fields[i] = columns[i].getName();
		}
		
		return db.query(true, table.getName(), fields, query, queryValues, null, null, order, null);
	}
	
	public static Cursor runCount(SQLiteDatabase db, DbTable table, DbField queryField, String queryValue) {
		String[] val = null;
		
		String query = "SELECT COUNT(" + table.getIdFieldName() + ") AS count FROM " + table.getName();
		if (queryField != null) {
			query += " WHERE " + queryField.getName() + "=?";
			val = wrapValue(queryValue);
		}
		
		return db.rawQuery(query, val);
	}
	
	public static Integer countChanges(SQLiteDatabase db) {
		Cursor cursor = db.rawQuery("SELECT changes()", null);
		
		int rowsChanged = 0;
		
		if (cursor.moveToFirst()) {
			rowsChanged = cursor.getInt(0);
		}
		closeCursor(cursor);
		
		return rowsChanged;
	}
	
	public static long updateOrInsertRow(SQLiteDatabase db, DbTable table, ContentValues cv, DbField queryField, String queryValue) {
		String[] fields = new String[]{table.getIdFieldName()};
		String query = wrapQuery(queryField);
		String[] val = wrapValue(queryValue);
		
		Cursor cursor = db.query(table.getName(), fields, query, val, null, null, null);
		
		boolean exists = cursor.moveToFirst();
		closeCursor(cursor);
		
		if (exists) {
			db.update(table.getName(), cv, query, val);
			return -1;
		} else {
			return db.insert(table.getName(), null, cv);
		}
	}
	
	public static long getIdForRow(SQLiteDatabase db, DbTable table, ContentValues cv, DbField[] queryFields) {
		String [] idField = new String[]{table.getIdFieldName()};
		String query = wrapQueries(queryFields);
		String [] val = wrapContentValues(cv, queryFields);
		
		Cursor cursor = db.query(table.getName(), idField, query, val, null, null, null);
		long id = 0;
		
		if (cursor.moveToFirst()) {
			id = cursor.getLong(0);
		} else {
			id = db.insert(table.getName(), null, cv);
		}
		
		closeCursor(cursor);
		
		return id;
	}
	
	public static void deleteRow(SQLiteDatabase db, DbTable table, DbField queryField, String queryValue) {
		String query = wrapQuery(queryField);
		String[] val = wrapValue(queryValue);
		
		db.delete(table.getName(), query, val);
	}
	
	public static void deleteRow(SQLiteDatabase db, DbTable table, DbField[] queryFields, String[] queryValues) {
		String query = wrapQueries(queryFields);
		
		db.delete(table.getName(), query, queryValues);
	}
	
	public static void changeTableValue(SQLiteDatabase db, DbTable table, ContentValues cv, DbField queryField, String queryValue) {
		db.update(table.getName(), cv, wrapQuery(queryField), wrapValue(queryValue));
	}
	
	public static void runRawSqlStatements(SQLiteDatabase db, List<String> sqls) {
		db.beginTransaction();
		
		try {
			for (String sql : sqls) {
				db.execSQL(sql);
			}
			db.setTransactionSuccessful();
		} catch (SQLException sqle) {
			Log.e(TAG, "Error running SQL list!", sqle);
		}
		
		db.endTransaction();
	}
	
	private static String wrapQuery(DbField queryField) {
		return (queryField != null) ? queryField.getName() + "=?" : null;
	}
	
	private static String[] wrapValue(String queryValue) {
		return (queryValue != null) ? new String[]{queryValue} : null;
	}
	
	private static String wrapQueries(DbField [] queryFields) {
		StringBuilder query = new StringBuilder();
		for (DbField field : queryFields) {
			String newQuery = wrapQuery(field);
			if (newQuery != null) {
				query.append(newQuery + " AND ");
			}
		}
		if (query.length() == 0) {
			return null;
		} else {
			query.setLength(query.length()-5);
			return query.toString();
		}
	}
	
	private static String[] wrapContentValues(ContentValues cv, DbField [] fields) {
		String [] values = new String[fields.length];
		for (int i = 0; i < fields.length; i++) {
			values[i] = cv.getAsString(fields[i].getName());
		}
		
		return values;
	}
	
	private static String wrapOrder(DbField orderBy) {
		return (orderBy != null) ? orderBy.getName() : null;
	}
	
	private static void closeCursor(Cursor cursor) {
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
	}
}
